﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;


namespace ITOMMS.Business
{
    /// <summary>
    /// DataAccessHelp 的摘要说明
    /// </summary>
    public class DataAccessHelp
    {
        private static string connszstring = System.Configuration.ConfigurationManager.AppSettings["itomsdb"];
        public DataAccessHelp()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }

        public static DataTable GetDataTable(string sql)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connszstring))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
                sda.Fill(dt);
                conn.Close();
            }

            return dt;
        }

        public static void ExecuteNonQuery(string sql)
        {
            SqlConnection conn = new SqlConnection(connszstring);
            SqlCommand cmd = new SqlCommand(sql, conn);
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        public static int ExecuteInsertWithID(string cmdText, SqlParameter[] cmdParms)
        {
            string ls_Sql = cmdText + ";SELECT @ID = @@identity;";
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connszstring);
            SetCommand(conn, cmd, CommandType.Text, ls_Sql, cmdParms);
            cmd.Parameters.Add("@ID", SqlDbType.Int, 4).Direction = ParameterDirection.Output;

            int result = cmd.ExecuteNonQuery();
            int id = int.Parse(cmd.Parameters["@ID"].Value + "");

            cmd.Parameters.Clear();
            conn.Close();
            return id;
        }

        private static void SetCommand(SqlConnection conn, SqlCommand cmd, CommandType cmdType, string cmdText, SqlParameter[] Parms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;

            if (Parms != null)
            {
                foreach (SqlParameter parm in Parms)
                {
                    cmd.Parameters.Add(parm);
                }
            }
        }

    }
}
